package com.yanyun.yczyy.api.repository.impl;

import com.yanyun.yczyy.api.entities.*;
import com.yanyun.yczyy.api.repository.IDeviceManagerRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Slf4j
@Repository
public class DeviceManagerRepository implements IDeviceManagerRepository {
    @Override
    public DeviceDoorStatusView doorStatus() {
        final String sql = "select count(1) total,sum(case when value = '门开' then 1 else 0 end) open_size,sum(case when value='门关' then 1 else 0 end) close_size from tbl_io_data a,( select distinct device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = '门禁系统' and resource_id <> 31822 group by device_id) b where a.device_id = b.device_id and a.io_time = b.io_time";
        try {
            return yczyy.queryForObject(sql, m_DeviceDeviceDoorStatusViewMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public List<DeviceCatchmentWallStatus> catchmentWallStatus() {
        final String sql = "select a.resource_name,a.io_time,a.`value` from tbl_io_data a,( select distinct device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = '集水井' and resource_id <> 31822 " + "group by device_id) b where a.device_id = b.device_id and a.io_time = b.io_time";
        try {
            return yczyy.query(sql, m_DeviceCatchmentWallStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public DeviceAirConditioningStatus airConditioningStatus() {
        final String sql = "select count(1) total, sum(case when value = '运行' then 1 else 0 end) effective, sum(case when value = '停止' then 1 else 0 end) ineffective  from tbl_io_data a,( select distinct device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = 'VRV空调' and resource_id <> 31822 group by device_id) b where a.device_id = b.device_id and a.io_time = b.io_time";
        try {
            return yczyy.queryForObject(sql, m_DeviceAirConditioningStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public List<DeviceAreaAirConditioningStatus> areaAirConditioningStatus() {
        final String sql = "select SUBSTRING_INDEX(resource_name,'_',1) location,count(1) total, sum(case when value = '运行' then 1 else 0 end) effective, sum(case when value = '停止' then 1 else 0 end) ineffective  from tbl_io_data a,( select distinct device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = 'VRV空调' and resource_id <> 31822 group by device_id) b where a.device_id = b.device_id and a.io_time = b.io_time group by SUBSTRING_INDEX(resource_name,'_',1)";
        try {
            return yczyy.query(sql, m_DeviceAreaAirConditioningStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public List<DeviceDepartmentCTPStatus> departmentCTPStatus() {
        final String sql = "SELECT DepartmentName department_name,     sum(case when DeviceOnline = 1 then '1' else 0 end) online_size,        sum(case when DeviceOnline = 0 then '1' else 0 end) offline_size FROM `departmentdevice` group by DepartmentName";
        try {
            return ctp.query(sql, m_DeviceDepartmentCTPStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public DeviceCameraStatus cameraStatus() {
        final String sql = "select count(1) total,sum(case when value = '在线' then 1 else 0 end) online_size,sum(case when value = '离线' then 1 else 0 end) offline_size  from tbl_io_data a,( select distinct device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = '视频监控系统' and resource_id <> 31822 group by device_id) b where a.device_id = b.device_id and a.io_time = b.io_time";
        try {
            return yczyy.queryForObject(sql, m_DeviceCameraStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public List<DeviceAreaCameraStatus> areaCameraStatus() {
        final String sql = "select case when locate('医研', resource_name) > 1 then '医研楼' else '门诊楼' end location, count(1) total,sum(case when value = '在线' then 1 else 0 end) online_size,sum(case when value = '离线' then 1 else 0 end) offline_size  from tbl_io_data a,( select distinct device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = '视频监控系统' and resource_id <> 31822 group by device_id) b where a.device_id = b.device_id and a.io_time = b.io_time group by case when locate('医研', resource_name) > 1 then '医研楼' else '门诊楼' end";
        try {
            return yczyy.query(sql, m_DeviceAreaCameraStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public List<DeviceElevatorFloorStatus> elevatorFloorStatus() {
        final String sql = "select a.resource_name,cast(a.value as FLOAT) floor from tbl_io_data a,( select resource_id, device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = '电梯系统' and resource_name like '%楼层显示' group by resource_id,device_id) b where a.device_id = b.device_id and a.io_time = b.io_time and a.resource_id = b.resource_id";
        try {
            return yczyy.query(sql, m_DeviceElevatorFloorStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    @Override
    public List<DeviceElevatorRunningStatus> elevatorRunningStatus() {
        final String sql = "select a.resource_name,a.value from tbl_io_data a,( select resource_id, device_id,max(io_time) io_time from tbl_io_data where io_time >= DATE_ADD(now(),interval -12 hour) and sys_name = '电梯系统' and resource_name like '%运行状态' group by resource_id,device_id) b where a.device_id = b.device_id and a.io_time = b.io_time and a.resource_id = b.resource_id ";
        try {
            return yczyy.query(sql, m_DeviceElevatorRunningStatusMapper);
        } catch (Exception e) {
            log.error("", e);
            return null;
        }
    }

    private final RowMapper<DeviceAirConditioningStatus> m_DeviceAirConditioningStatusMapper = new BeanPropertyRowMapper<>(DeviceAirConditioningStatus.class);
    private final RowMapper<DeviceAreaAirConditioningStatus> m_DeviceAreaAirConditioningStatusMapper = new BeanPropertyRowMapper<>(DeviceAreaAirConditioningStatus.class);
    private final RowMapper<DeviceCameraStatus> m_DeviceCameraStatusMapper = new BeanPropertyRowMapper<>(DeviceCameraStatus.class);
    private final RowMapper<DeviceAreaCameraStatus> m_DeviceAreaCameraStatusMapper = new BeanPropertyRowMapper<>(DeviceAreaCameraStatus.class);
    private final RowMapper<DeviceCatchmentWallStatus> m_DeviceCatchmentWallStatusMapper = new BeanPropertyRowMapper<>(DeviceCatchmentWallStatus.class);
    private final RowMapper<DeviceDepartmentCTPStatus> m_DeviceDepartmentCTPStatusMapper = new BeanPropertyRowMapper<>(DeviceDepartmentCTPStatus.class);
    private final RowMapper<DeviceDoorStatusView> m_DeviceDeviceDoorStatusViewMapper = new BeanPropertyRowMapper<>(DeviceDoorStatusView.class);
    private final RowMapper<DeviceElevatorRunningStatus> m_DeviceElevatorRunningStatusMapper = new BeanPropertyRowMapper<>(DeviceElevatorRunningStatus.class);
    private final RowMapper<DeviceElevatorFloorStatus> m_DeviceElevatorFloorStatusMapper = new BeanPropertyRowMapper<>(DeviceElevatorFloorStatus.class);


    @Autowired
    JdbcTemplate yczyy;
    @Autowired
    JdbcTemplate ctp;
}
